Public Class frmBaoCaoHangTonKho
    Private loai_baocao As String
    Dim loai_kho As String = ""
    Dim ma_hang As String = ""

    Public Sub New(ByVal loaibaocao As String)
        'KeHoach va TaiChinh

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        loai_baocao = loaibaocao
    End Sub

    Private Sub frmBaoCaoHangTonKho_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txtLoaiKho.Focus()
        KhoiTaocbbLoaiBaoCao()
    End Sub

    Public Sub KhoiTaocbbLoaiBaoCao()
        cbbLoaiBaoCao.Items.Add("Trang Bị - Dụng Cụ")
        cbbLoaiBaoCao.Items.Add("Thuốc - Hóa Chất - Bông Băng")
    End Sub

#Region "txtLoaiKho"
    Private Sub txtLoaiKho_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLoaiKho.DoubleClick
        HienDMLoaiKho()
    End Sub

    Private Sub txtLoaiKho_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLoaiKho.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            HienDMLoaiKho()
        End If
    End Sub

    Public Sub HienDMLoaiKho()
        Dim strDMLoaiKho As String
        strDMLoaiKho = "select KH,TEN from tblDMKhac where L = 'L'"
        Dim ma, ten As String
        ma = ""
        ten = ""
        If (txtLoaiKho.Text <> "") Then
            Dim strSql As String
            strSql = "select TEN from tblDMKhac where L = 'L' and KH = '" & txtLoaiKho.Text & "'"
            Dim ten_loaikho As String
            ten_loaikho = BaseDB.ExecSql_DataValue(strSql)
            If (Not ten_loaikho Is Nothing) Then
                loai_kho = txtLoaiKho.Text
                txtLoaiKho.Text = ten_loaikho
            Else
                'hien DM LoaiKho
                HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
                loai_kho = ma
                txtLoaiKho.Text = ten
            End If
        Else
            'hien DM LoaiKho
            HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
            loai_kho = ma
            txtLoaiKho.Text = ten
        End If
    End Sub

    Public Sub HienDM(ByVal strsql As String, ByVal strDM As String, ByVal strVM As String, _
                ByRef DM As String, ByRef VM As String)
        Dim frm As New frmChonDanhMucTheoListBox(strsql, strDM, strVM, "")
        frm.ShowDialog()
        DM = frm.DisplayMember
        VM = frm.ValueMember
    End Sub
#End Region

#Region "txtLoaiThuoc"
    Private Sub txtLoaiThuoc_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLoaiThuoc.DoubleClick
        If (loai_kho = "1") Then ' kho thuong xuyen
            ShowFrmChonLoaiThuoc()
        Else
            ShowMessages("Chỉ sử dụng được đối với loại kho thường xuyên")
        End If
    End Sub

    Private Sub txtLoaiThuoc_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLoaiThuoc.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            If (loai_kho = "1") Then ' kho thuong xuyen
                ShowFrmChonLoaiThuoc()
            Else
                ShowMessages("Chỉ sử dụng được đối với loại kho thường xuyên")
            End If
        ElseIf (e.KeyCode = Keys.Delete) And (e.Modifiers = Keys.Control) Then
            ma_hang = ""
        End If
    End Sub

    Public Sub ShowFrmChonLoaiThuoc()
        Dim frm As New frmChonLoaiThuoc
        frm.ShowDialog()
        If (frm.Ten <> Nothing) Then
            Dim ten As String
            ten = frm.Ten
            txtLoaiThuoc.Text = ten
            If (frm.Loai) Then
                'm_maloaithuoc = CLoaiThuocs.GetLoaiThuocByTen(ten).MaLoaiThuoc
                ma_hang = CLoaiThuocs.GetLoaiThuocByTen(ten).MaLoaiThuoc
            Else
                Dim str() As String
                str = ten.Split("-")
                If (str.Length = 2) Then
                    ma_hang = CThuocs.GetThuocByTenandDVT(str(0).Trim, str(1).Trim).MaThuoc
                End If
            End If
        End If
    End Sub
#End Region

    Private Sub btnBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBaoCao.Click
        If (loai_kho <> "") Then
            Dim ds As DataSet
            ds = GetDSHangTonKho()
            Dim nnreport As New rptBaoCaoHangTonKho
            nnreport.SetDataSource(ds.Tables(0))
            nnreport.SetParameterValue(0, txtLoaiKho.Text)
            nnreport.SetParameterValue(1, loai_baocao)
            Dim frm As New frmReportViewer(nnreport)
            frm.ShowDialog()
        Else
            ShowMessages("Bạn chưa chọn Loại kho cần báo cáo")
        End If
    End Sub

    Public Function GetDSHangTonKho() As DataSet
        Dim strsql As String
        strsql = "select * from viewHangTonKho where lk = '" & loai_kho & "' "
        If (ma_hang <> "") Then
            If (ma_hang.Substring(7, 7) = "0000000") Then 'loai thuoc
                'xuli chuoi: loai bo nhung ki tu 0 dung sau de lay duoc loai thuoc
                Dim loaithuoc As String = ma_hang.Substring(0, 7)
                For i As Integer = 0 To 6
                    If (loaithuoc.Chars(6 - i) = "0") Then
                        loaithuoc = loaithuoc.Remove(6 - i, 1)
                    Else
                        Exit For
                    End If
                Next
                'lay left
                strsql &= " and left(ma," + CStr(loaithuoc.Length) + ") = '" + loaithuoc + "'"
            Else 'thuoc
                strsql &= " and ma = '" + ma_hang + "'"
            End If
        End If

        If (cbbLoaiBaoCao.SelectedIndex = 0) Then ' trang bi - dung cu
            strsql &= " and (left(ma,3) = 'Y01' or left(ma,3) = 'Y02') "
        ElseIf (cbbLoaiBaoCao.SelectedIndex = 1) Then 'thuoc - bb - hc
            strsql &= " and (left(ma,3) = 'Y05' or left(ma,3) = 'Y06' or left(ma,3) = 'Y07' or left(ma,3) = 'Y08') "
        End If
        If (loai_baocao = "kehoach") Then
            strsql &= " and sltonkh > '0' order by maloaithuoc "
        Else
            strsql &= " and sltonss <> '0' order by maloaithuoc "
        End If
        Dim ds As DataSet
        ds = BaseDB.ExecSql_DataSet(strsql)
        Return ds
    End Function

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Dim mFileName2 As String = Application.StartupPath & "\TempBAOCAOTONKHO.xls"
        Dim ds As DataSet
        ds = GetDSHangTonKho()
        ToEXCEL(mFileName2, ds)
    End Sub

    Private Sub ToEXCEL(ByVal mFileName2 As String, ByVal m_ds As DataSet)
        Dim mFileName As String = Application.StartupPath & "\BAOCAOTONKHO.xls"

        System.IO.File.Copy(mFileName, mFileName2, True)
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Open(mFileName2), Excel.Workbook)

        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        xlSheet.Activate()
        xlSheet.Application.Visible = True

        'dau phieu
        xlSheet.Cells(2, 1) = "Kho Hàng :" + txtLoaiKho.Text
        xlSheet.Cells(2, 3) = "Tính đến ngày :" + CStr(Now.Day) + " tháng " + CStr(Now.Month) + " năm " + Now.Year.ToString

        'than phieu

        Try
            Dim iRow0 As Integer = 6
            Dim len, i, j, stt As Integer
            Dim ma_loaithuoc As String

            i = 0
            j = 0
            len = m_ds.Tables(0).Rows.Count
            '(i chay trong ds, j chay trong excel)
            While (i < len)
                If (i >= len) Then
                    Exit While
                End If
                ma_loaithuoc = m_ds.Tables(0).Rows.Item(i).Item("MaLoaiThuoc")
                xlSheet.Cells(iRow0 + j, 2) = m_ds.Tables(0).Rows.Item(i).Item("ten_loaithuoc")
                mRange = xlSheet.Cells(iRow0 + j, 2)
                mRange.Font.Bold = True
                j += 1
                stt = 1
                While (m_ds.Tables(0).Rows.Item(i).Item("MaLoaiThuoc") = ma_loaithuoc)
                    xlSheet.Cells(iRow0 + j, 1) = stt
                    xlSheet.Cells(iRow0 + j, 2) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("ten"))
                    xlSheet.Cells(iRow0 + j, 3) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("lo"))
                    xlSheet.Cells(iRow0 + j, 4) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("HanSD"))
                    xlSheet.Cells(iRow0 + j, 5) = ChuanHoaChuoi(m_ds.Tables(0).Rows.Item(i).Item("DVT"))
                    xlSheet.Cells(iRow0 + j, 6) = m_ds.Tables(0).Rows.Item(i).Item("h")
                    xlSheet.Cells(iRow0 + j, 7) = m_ds.Tables(0).Rows.Item(i).Item("gle")
                    xlSheet.Cells(iRow0 + j, 8) = m_ds.Tables(0).Rows.Item(i).Item("sltonss")
                    xlSheet.Cells(iRow0 + j, 9) = m_ds.Tables(0).Rows.Item(i).Item("sltonth")
                    stt += 1
                    i += 1
                    j += 1
                    If (i >= len) Then
                        Exit While
                    End If
                End While
                '
            End While

            xlApp.ActiveWindow.Visible = False
            xlApp.Windows(1).Activate()
        Catch ex As Exception
            MsgBox(ex.Source)
        End Try
        'duoi(phieu)
        'xlSheet.Cells(iRow0 + i + 1, 2) = "Số lượng phiếu Nhập : " + CStr(nhap)
        'xlSheet.Cells(iRow0 + i + 2, 2) = "Số lượng phiếu Xuất : " + CStr(xuat)
        'xlSheet.Cells(iRow0 + i + 1, 4) = "Nhập hàng từ: " + CStr(so_donvinhap)
        'xlSheet.Cells(iRow0 + i + 2, 4) = "Xuất hàng cho : " + CStr(so_donvixuat)
        'xlSheet.Cells(iRow0 + i + 4, 5) = "In Ngày " + CStr(Now.Day) + "/" + CStr(Now.Month) + "/" + CStr(Now.Year)
    End Sub
End Class